package com.swufe.scoresforapp.Yonghu;

import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Dataoutput {
    /*
    启动数据库需要用到的一些信息设置
     */
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://192.168.1.5/xinxi?seUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&verifyServerCertificate=false&useSSL=false";
    // 数据库的用户名与密码，需要根据自己的设置 此处的ip为本机ip
    static final String USER = "root";
    static final String PASS = "199802";
    static Connection conn = null;
    static Statement stmt = null;

    //建立链接
    public void open(){
        try{
            // 注册 JDBC 驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 打开链接
            //连接数据库..."
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            // 执行查询  实例化Statement对象...
            stmt = conn.createStatement();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }
    //完成操作时关闭链接
    public void close(){
        try {
            stmt.close();
            conn.close();
        }
        catch(Exception e) {}
    }


    //对用户信息表操作
    /*******************************************************/
    //根据用户名称写入用户信息
    //写入密码
    public void setPassword(String name, String password){
        open();
        String sql = "update 用户信息表 set 密码 = \""+password+" \"where 用户账号 =\""+name+"\"";;
        try{
           stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }
    //写入生源地
    public void setArea(String name, String area){
        open();
        String sql = "update 用户信息表 set  生源地 = \""+area+" \"where 用户账号 =\""+name+"\"";;
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }
    //写入分数
    public void setScore(String name, String score){
        open();
        String sql = "update 用户信息表 set  分数 = \""+score+" \"where 用户账号 =\""+name+"\"";
       // Log.i("Sqltest"sql)
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }
    //写入考试科类
    public void setType(String name, String type){
        open();
        String sql = "update 用户信息表 set  学科类别 = \""+type+" \"where 用户账号 =\""+name+"\"";;
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }
    //写入考试年份（计算接口）
    public void setYear(String name, String year){
        open();
        String sql = "update 用户信息表 set  年份= \""+year+" \"where 用户账号 =\""+name+"\"";;
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }
    //写入排名
    public void setOrder(String name, String order){
        open();
        String sql = "update 用户信息表 set  排名 = \""+order+" \"where 用户账号 =\""+name+"\"";;
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }
    //写入性别
    public void setSex(String name, String sex){
        open();
        String sql = "update 用户信息表 set  性别 = \""+sex+" \"where 用户账号 =\""+name+"\"";;
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }

    //新用户注册/用户更改信息,写入用户信息表：
    public void setYonghu(String name, String area, String type, String score, String order, String sex, String year, String password){
        open();
        String sql = "insert into 用户信息表(用户账号,密码,生源地,分数,学科类别,年份,排名,性别) values" +
                "( \""+name+"\",\""+password+"\",\""+area+"\",\""+score+"\",\""+type+"\",\""+year+"\",\""+order+"\",\""+sex+"\")";
        Log.i("sql:",sql);
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
        }
        close();
    }



    //对用户学校收藏表操作
    /*******************************************************/
    /*******************************************************/
    public String deletShoucang(String school,String yonghu){
        open();
        String sql = "delete from 收藏学校 where 学校名称 =\""+school+"\""+"and 用户账号="+"\""+yonghu+"\"";
        Log.i("sql:",sql);
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
            close();
            return "删除失败";
        }
        close();
        return "删除成功";
    }
    public String setShoucang(String school,String yonghu){
        open();
        String sql = "insert into 收藏学校(用户账号,学校名称,录取概率)  values(\""+yonghu+"\""+","+"\""+school+"\",\"\")";
        Log.i("sql:",sql);
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
            close();
            return "收藏失败";
        }
        close();
        return "收藏成功";
    }

    //对用户专业收藏表操作
    /*******************************************************/
    /*******************************************************/
    public String deletMajorShoucang(String school,String major,String yonghu){
        open();
        String sql = "delete from 收藏专业 where 学校名称 =\""+school+"\" and 用户账号="+"\""+yonghu+"\"and 专业名称="+"\""+major+"\"";
        Log.i("sql:",sql);
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
            close();
            return "删除失败";
        }
        close();
        return "删除成功";
    }
    public String setMajorShoucang(String school,String yonghu,String major){
        open();
        String sql = "insert into 收藏专业(用户账号,专业名称,学校名称)  values (\""+yonghu+"\""+",\""+major+"\",\""+school+"\")";
        //Log.i("sql:",sql);
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
            close();
            return "收藏失败";
        }
        close();
        return "收藏成功";
    }

    //对用户对比表格收藏的删除
    /*******************************************************/
    /*******************************************************/
    public String deletCompareShoucang(String yonghu,String school1,String school2,String school3,String school4) {
        open();
        school1=school1.replace("1.","");
        school1=school1.trim();
        if(school1.equals("null"))school1 = " is null";
        else school1 = "=\""+school1+"\"";
        school2=school2.replace("2.","");
        school2=school2.trim();
        if(school2.equals("null"))school2 = " is null";
        else school2 = "=\""+school2+"\"";
        school3=school3.replace("3.","");
        school3=school3.trim();
        if(school3.equals("null"))school3 = " is null";
        else school3 = "=\""+school3+"\"";
        school4=school4.replace("4.","");
        school4=school4.trim();
        if(school4.equals("null"))school4 = " is null";
        else school4 = "=\""+school4+"\"";
        String sql = "delete from 收藏对比 where 用户账号 =\""+yonghu+"\" and 学校1 "+school1+
                "and 学校2 "+school2+"and 学校3 "+school3+" and 学校4 "+school4;
        Log.i("sql:",sql);
        try{
            stmt.executeUpdate(sql);
        } catch(SQLException e){
            Log.i("error",""+e);
            close();
            return "删除失败";
        }
        close();
        return "删除成功";
    }
}
